<?php
	$servername = "118.24.14.224";
	$username = "root";
	$password = "root";
	$dbname = "carStation";
	$conn = null;
	try {
    	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    	$stmt = $conn->prepare("SET NAMES utf8"); 
    	$stmt->execute();
	}
	catch(PDOException $e)
	{
    	echo $e->getMessage();
	}

	function userLogin($name,$passwd){
		global $conn;
    	$stmt = $conn->prepare("SELECT * FROM user_info where user_name='$name' and user_passwd='$passwd'"); 
    	$stmt->execute();
    	$row=$stmt->fetchAll();
    	if(empty($row)) return 0;
    	else return 1;
	}

	function stationMangerReg($name,$passwd,$email){
		global $conn;
    	$stmt = $conn->prepare("INSERT INTO station_manager values('$name','$passwd','$email')"); 
    	$stmt->execute();
    	return true;
	}

	function userRegister($name,$passwd,$carNum,$mobile){
		global $conn;
    	$stmt = $conn->prepare("INSERT INTO user_info values('$name','$passwd','$carNum','$mobile',null,null,null)"); 
    	$stmt->execute();
    	return true;
	}

	function setStation($name,$amount,$price,$remain,$lat,$lng,$mName){
		global $conn;
    	$stmt = $conn->prepare("INSERT INTO station_info values('$name','$amount','$price','$remain',$lat,$lng,'$mName')");
    	$stmt->execute();
    	return true;
	}

	function updateLoc($uid,$lat,$lng){
		global $conn;
    	$stmt = $conn->prepare("UPDATE user_info set user_lat=$lat,user_lng=$lng where user_id=$uid"); 
    	$stmt->execute();
    	return true;
	}

	function getAllStations(){
		global $conn;
    	$stmt = $conn->prepare("SELECT * FROM station_info"); 
    	$stmt->execute();
    	$row=$stmt->fetchAll();
    	if(empty($row)) return null;
    	else return json_encode($row);
	}

	function getStations($lat,$lng){
		global $conn;
    	$stmt = $conn->prepare("SELECT * FROM station_info where gpsdistance($lat,$lng,s_lat,s_lng)<1000"); 
    	$stmt->execute();
    	$row=$stmt->fetchAll();
    	if(empty($row)) return null;
    	else return json_encode($row);
	}

	function setCost($intime,$outtime,$cost,$carNum,$sName){
		global $conn;
    	$stmt = $conn->prepare("INSERT INTO station_cost_log values('$intime','$outtime','$cost','$carNum','$sName',null)"); 
    	$stmt->execute();
    	return true;
	}

	function getCost($mName){
		global $conn;
    	$stmt = $conn->prepare("SELECT s_amount,s_price,sum(cost) as cost,station_name FROM station_cost_log,station_info where manager_name='$mName' and station_name=s_name group by station_name"); 
    	$stmt->execute();
    	$row=$stmt->fetchAll();
    	if(empty($row)) return null;
    	else return json_encode($row);
	}

	function getCostByHour($sName){
		global $conn;
    	$stmt = $conn->prepare("select sum(cost) as cost,date_format(intime, '%Y-%m-%d-%H') as hour from station_cost_log where station_name='$sName' group by date_format(intime, '%Y-%m-%d-%H')"); 
    	$stmt->execute();
    	$row=$stmt->fetchAll();
    	if(empty($row)) return null;
    	else return json_encode($row);
	}

	function destory(){
		global $conn;
		$conn->close();
	}

?>

